package com.project;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.regex.*;

public class DB_Ticket extends DB_Operator {

    /**
     * Default constructor
     */
    public DB_Ticket() {
    }

    // 实现添加方法
    /**
     * @param input 输入
     * @return 返回
     */
    public boolean add(List<String> input) {
        if (input.size() != 3) { // 1 个 oid, 1 个 uid, 1 个 set
            return false;
        }
        connect();
        String sql = "INSERT INTO `Ticket` (`oid`, `uid`, `set`, `state`) VALUES (?,?,?, 0)";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setInt(1, Integer.parseInt(input.get(0)));
            PStmt.setInt(2, Integer.parseInt(input.get(1)));
            PStmt.setString(3, input.get(2));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    // 实现删除方法
    /**
     * @param input 输入
     * @return 返回
     */
    public boolean delete(List<String> input) {
        if (input.isEmpty()) {
            return false;
        }
        connect();
        String deleteSql = "DELETE FROM `Ticket` WHERE `id` =?";
        String maxIdSql = "SELECT MAX(id) FROM `Ticket`";
        String updateAutoIncrementSql = "ALTER TABLE `Ticket` AUTO_INCREMENT =?";
        try (PreparedStatement deletePStmt = con.prepareStatement(deleteSql);
             PreparedStatement maxIdPStmt = con.prepareStatement(maxIdSql);
             PreparedStatement updatePStmt = con.prepareStatement(updateAutoIncrementSql)) {
            deletePStmt.setInt(1, Integer.parseInt(input.getFirst()));
            int rowsAffected = deletePStmt.executeUpdate();
    
            ResultSet rs = maxIdPStmt.executeQuery();
            int maxId = 1;
            if (rs.next()) {
                maxId = rs.getInt(1) + 1;
            }
            updatePStmt.setInt(1, maxId);
            updatePStmt.executeUpdate();
    
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public List<String> find(List<String> input) {
        List<String> result = new ArrayList<>();
        connect();
        StringBuilder sql = new StringBuilder("SELECT * FROM Ticket WHERE 1=1");
        List<Object> params = new ArrayList<>();
    
        try {
            for (String condition : input) {
                Pattern pattern = Pattern.compile("(<=|>=|=|<|>)");
                Matcher matcher = pattern.matcher(condition);
                if (matcher.find()) {
                    String operator = matcher.group();
                    String[] parts = condition.split(Pattern.quote(operator), 2);
                    String field = parts[0].trim().toLowerCase();
                    String value = parts[1].trim();
    
                    if (!Set.of("id", "oid", "uid", "set", "state").contains(field)) continue;
    
                    if (field.matches("id|state|oid|uid")) {
                        try {
                            params.add(Integer.parseInt(value));
                        } catch (NumberFormatException e) {
                            continue;
                        }
                    } else {
                        params.add(value);
                    }
                    sql.append(" AND ").append(field).append(operator).append("?");
                }
            }

            sql.append(" ORDER BY id ASC");
    
            try (PreparedStatement PStmt = con.prepareStatement(sql.toString())) {
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);
                    if (param instanceof Integer) {
                        PStmt.setInt(i + 1, (Integer) param);
                    } else {
                        PStmt.setString(i + 1, param.toString());
                    }
                }
    
                ResultSet rs = PStmt.executeQuery();
                while (rs.next()) {
                    String row = String.join(",",
                            rs.getString("id"),
                            rs.getString("oid"),
                            rs.getString("uid"),
                            rs.getString("set"),
                            rs.getString("state")
                    );
                    result.add(row);
                }
            }
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
        } finally {
            close();
        }
        return result;
    }

    /**
     * @param conditionColumn 条件
     * @param conditionValue 值
     * @param returnList 返回
     */
    public void find(String conditionColumn, String conditionValue, List<String> returnList) {
        connect();
        String sql = "SELECT * FROM `Ticket` WHERE ture";
        if (!conditionColumn.isEmpty() && !conditionValue.isEmpty()) {
            sql += " AND `" + conditionColumn + "` =?";
        }
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            if (!conditionColumn.isEmpty() && !conditionValue.isEmpty()) {
                PStmt.setString(1, conditionValue);
            }
            ResultSet rs = PStmt.executeQuery();
            while (rs.next()) {
                returnList.add(rs.getString("id"));
                returnList.add(rs.getString("oid"));
                returnList.add(rs.getString("uid"));
                returnList.add(rs.getString("set"));
                returnList.add(rs.getString("state"));
            }
            close();
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
        }
    }

    // 实现更新方法
    /**
     * @param input 输入
     * @return 返回
     */
    public boolean update(List<String> input) {
        if (input.size() != 5) { // 1 个 id, 1 个 oid, 1 个 uid, 1 个 set, 1 个 state
            return false;
        }
        connect();
        String sql = "UPDATE `Ticket` SET `oid` =?, `uid` =?, `set` =?, `state` =? WHERE `id` =?";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setInt(1, Integer.parseInt(input.get(1)));
            PStmt.setInt(2, Integer.parseInt(input.get(2)));
            PStmt.setString(3, input.get(3));
            PStmt.setInt(4, Integer.parseInt(input.get(4)));
            PStmt.setInt(5, Integer.parseInt(input.get(0)));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }
}